/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package SubFeeHead;

import FeeType.FeeTypeBean;
import java.sql.*;
import java.util.Vector;
/**
 *
 * @author mishra
 */
    public class Query {
        
    static Connection con=null;
    
     public static int add(SubFeeHeadBean sfb) throws SQLException
    {
        int i=0,k=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into SUB_FEE_TYPE values(SUBFEETYPEID.nextval,?,?,?,sysdate)");
            pre.setInt(1, sfb.getFeeTypeId());
            pre.setString(2, sfb.getSubFeeType());
            pre.setInt(3, sfb.getDuration());
            k=pre.executeUpdate();
            if(k!=0)
            {
                i=getMaxId();
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return i;
    }
    public static int getMaxId() throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select max(SUB_FEE_TYPE_ID) from SUB_FEE_TYPE");
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                i=rs.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }finally{
            con.close();
        }
        return i;
    }
public static SubFeeHeadBean viewSpecific(int id) throws SQLException
{
    SubFeeHeadBean sfb= new SubFeeHeadBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from SUB_FEE_TYPE left join FEE_TYPE on SUB_FEE_TYPE.FEE_TYPE_ID=FEE_TYPE.FEE_TYPE_ID where SUB_FEE_TYPE=?");
        pre.setInt(1, id);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            FeeTypeBean ftb=new FeeTypeBean();
            sfb.setSubFeeTypeId(rs.getInt(1));
            sfb.setFeeTypeId(rs.getInt(2));
            sfb.setSubFeeType(rs.getString(3));
            sfb.setDuration(rs.getInt(4));
            sfb.setEntryDate(rs.getDate(5));
            ftb.setFeeTypeId(rs.getInt(6));
            ftb.setFeeTypeName(rs.getString(7));
            ftb.setEntryDate(rs.getDate(8));
            sfb.setFeeTypeBean(ftb);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return sfb;
}
    
public static Vector viewAll() throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from SUB_FEE_TYPE left join FEE_TYPE on SUB_FEE_TYPE.FEE_TYPE_ID=FEE_TYPE.FEE_TYPE_ID");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
           SubFeeHeadBean sfb= new SubFeeHeadBean();
           FeeTypeBean ftb=new FeeTypeBean();
            sfb.setSubFeeTypeId(rs.getInt(1));
            sfb.setFeeTypeId(rs.getInt(2));
            sfb.setSubFeeType(rs.getString(3));
            sfb.setDuration(rs.getInt(4));
            sfb.setEntryDate(rs.getDate(5));
            ftb.setFeeTypeId(rs.getInt(6));
            ftb.setFeeTypeName(rs.getString(7));
            ftb.setEntryDate(rs.getDate(8));
            sfb.setFeeTypeBean(ftb);
            list.add(sfb);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
}


public static Vector viewAll(int id) throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from SUB_FEE_TYPE left join FEE_TYPE on SUB_FEE_TYPE.FEE_TYPE_ID=FEE_TYPE.FEE_TYPE_ID where sub_fee_type.fee_type_id=?");
        pre.setInt(1, id);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
           SubFeeHeadBean sfb= new SubFeeHeadBean();
           FeeTypeBean ftb=new FeeTypeBean();
            sfb.setSubFeeTypeId(rs.getInt(1));
            sfb.setFeeTypeId(rs.getInt(2));
            sfb.setSubFeeType(rs.getString(3));
            sfb.setDuration(rs.getInt(4));
            sfb.setEntryDate(rs.getDate(5));
            ftb.setFeeTypeId(rs.getInt(6));
            ftb.setFeeTypeName(rs.getString(7));
            ftb.setEntryDate(rs.getDate(8));
            sfb.setFeeTypeBean(ftb);
            list.add(sfb);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
} 
public static Vector viewAll(String feetype) throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from SUB_FEE_TYPE left join FEE_TYPE on SUB_FEE_TYPE.FEE_TYPE_ID=FEE_TYPE.FEE_TYPE_ID where fee_type.fee_type_name=?");
        pre.setString(1,feetype);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
           SubFeeHeadBean sfb= new SubFeeHeadBean();
           FeeTypeBean ftb=new FeeTypeBean();
            sfb.setSubFeeTypeId(rs.getInt(1));
            sfb.setFeeTypeId(rs.getInt(2));
            sfb.setSubFeeType(rs.getString(3));
            sfb.setDuration(rs.getInt(4));
            sfb.setEntryDate(rs.getDate(5));
            ftb.setFeeTypeId(rs.getInt(6));
            ftb.setFeeTypeName(rs.getString(7));
            ftb.setEntryDate(rs.getDate(8));
            sfb.setFeeTypeBean(ftb);
            list.add(sfb);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
} 
public static int edit(SubFeeHeadBean sfb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update SUB_FEE_TYPE set FEE_TYPE_ID=?, SUB_FEE_TYPE=?, DURATION=?, ENTRY_DATE=sysdate where SUB_FEE_TYPE_ID=?");
            pre.setInt(1, sfb.getFeeTypeId());
            pre.setString(2, sfb.getSubFeeType());
            pre.setInt(3, sfb.getDuration());
            pre.setInt(4, sfb.getSubFeeTypeId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from SUB_FEE_TYPE where SUB_FEE_TYPE_ID=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }  
    public static SubFeeHeadBean viewSpecificSearch(int id, String val) throws SQLException
{
    SubFeeHeadBean sfb= new SubFeeHeadBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from SUB_FEE_TYPE left join FEE_TYPE on SUB_FEE_TYPE.FEE_TYPE_ID=FEE_TYPE.FEE_TYPE_ID where fee_type.fee_type_id=? and upper(SUB_FEE_TYPE.SUB_FEE_TYPE) like upper('?%')");
        pre.setInt(1, id);
        pre.setString(2, val);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            FeeTypeBean ftb=new FeeTypeBean();
            sfb.setSubFeeTypeId(rs.getInt(1));
            sfb.setFeeTypeId(rs.getInt(2));
            sfb.setSubFeeType(rs.getString(3));
            sfb.setDuration(rs.getInt(4));
            sfb.setEntryDate(rs.getDate(5));
            ftb.setFeeTypeId(rs.getInt(6));
            ftb.setFeeTypeName(rs.getString(7));
            ftb.setEntryDate(rs.getDate(8));
            sfb.setFeeTypeBean(ftb);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return sfb;
}
}


